package com.ibm.ems.dao;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import com.ibm.ems.dto.PollingBean;
import com.ibm.ems.dto.TopicBean;
import com.ibm.ems.dto.UserBean;

public class PollingDAO {
	Connection conn;

	public PollingDAO() throws ClassNotFoundException, SQLException {
		Class.forName("oracle.jdbc.OracleDriver");

		Driver d = new oracle.jdbc.OracleDriver();

		DriverManager.registerDriver(d);

		conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521:oracle", "hr", "hr");

	}

	public ArrayList<TopicBean> initiatePolling(String userName)
			throws SQLException {

		/*
		 * select all the courses that the user has to vote for/against set all
		 * the course names, desc and ids
		 */

		ArrayList<TopicBean> topicList = new ArrayList<TopicBean>();

		UserBean user = new UserBean();
		user.setUserName("userName"); // use the name from sessions

		String dsql = "select * from TOPIC where status = \"open\" and TOPIC_ID NOT IN (select TOPIC_ID from POLLING where USER_NAME= ?)";// \""+userName+"\") ";

		/*
		 * the query returns the details from topic table for those events for
		 * which polling is still open and user has not voted for/against
		 */

		PreparedStatement ps = conn.prepareStatement(dsql);
		ps.setString(1, user.getUserName());
		ResultSet rs = ps.executeQuery();

		TopicBean topic = new TopicBean();

		while (rs.next()) {
			topic.setTopicName(rs.getString("topic_name"));
			topic.setTopicId(rs.getString("topic_id"));
			topic.setCategory(rs.getString("topic_category"));
			topic.setDescription(rs.getString("description"));
			topicList.add(topic);
		}

		/*
		 * topic = new TopicBean(); topic.setTopicName("blah blah");
		 * topic.setCategory("categoryName"); topic.setTopicId("321");
		 * topic.setDescription("this course will tell how to blah blah!");
		 * topicList.add(topic);
		 */
		return topicList;
	}

	public void commitPolling(ArrayList<PollingBean> pollList)
			throws SQLException {
		PreparedStatement ps = conn
				.prepareStatement("insert into polling values (?,?,?)");
		for (Iterator iterator = pollList.iterator(); iterator.hasNext();) {
			PollingBean pollingBean = (PollingBean) iterator.next();
			ps.setString(0, pollingBean.getUserName());
			ps.setString(1, pollingBean.getTopicId());
			ps.setString(2, (pollingBean.getVote() ? "yes" : "no"));
			ps.execute();
		}
	}
}